package hk.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.*;
import java.util.List;
import java.util.Properties;

/**
 * @version 1.0
 * @Author YangJiaQuan_hrbnu
 * @Date 2024/4/2 20:52
 */
public class DBUtil {

    private static DataSource dataSource;

    //构造方法私有化,防止有人欠欠的new本类的实例
    private DBUtil() {}

    //在静态块中加载驱动类，这是Druid数据源
    static {
        Properties pro = new Properties();
        try {
            pro.load(new FileInputStream("D:\\Java后端数据库\\NEW_JAVADATEBASE\\Highcom_DateBase\\my-dbutil-jdbc\\src\\db.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        //dataSource=new ComboPooledDataSource("mysql");这是c3p0数据源
    }
    /**
     * 万能更新
     * @param sql 要执行的sql语句
     * @param params 参数列表
     * @return 数据库受影员的行数
     */
    public static int update(String sql, Object ... params) {
        Connection conn=null;

        try {
            conn=DBUtil.getConn();
            QueryRunner qr=new QueryRunner();
            return qr.update(conn, sql, params);

        }catch(Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        }finally {
            close(conn);
        }
    }

    /**
     * 添加数据,然后返回生成的自增id
     * @param sql
     * @param params
     * @return 生成的自增ID
     */
    public static int addAndReturnId(String sql, Object ... params) {
        int id=0;
        Connection conn=null;
        PreparedStatement stm=null;

        try {
            conn=DBUtil.getConn();
            stm=conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            for(int i=0;i<params.length;i++) {
                stm.setObject(i+1, params[i]);
            }

            stm.executeUpdate();

            ResultSet rs=stm.getGeneratedKeys();
            if(rs.next()) {
                id= rs.getInt(1);
            }

        }catch(Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        }finally {
            close(conn);
        }

        return id;
    }

    /**
     * 万能查询
     * @param <T> 泛型
     * @param sql
     * @param clazz 要返回的结果是什么类型
     * @param params 参数
     * @return 一个对象
     */
    public static <T> T  getSingleObj(String sql,Class<T> clazz, Object ... params ) {
        Connection conn=null;

        try {
            conn=DBUtil.getConn();
            QueryRunner qr=new QueryRunner();
            return qr.query(conn,sql, new BeanHandler<T>(clazz), params);

        }catch(Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        }finally {
            close(conn);
        }
    }

    /**
     * 万能查询,查询一组对象
     * @param <T>
     * @param sql
     * @param clazz
     * @param params
     * @return
     */
    public static <T> List<T> getList(String sql, Class<T> clazz, Object ... params ) {
        Connection conn=null;

        try {
            conn=DBUtil.getConn();
            QueryRunner qr=new QueryRunner();
            return qr.query(conn,sql, new BeanListHandler<T>(clazz), params);

        }catch(Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        }finally {
            close(conn);
        }
    }

    /**
     * 返回单个值(标量)
     * @param <T>
     * @param sql
     * @param params
     * @return
     */
    public static <T> T getScala(String sql,Object ... params) {
        Connection conn=null;
        try {
            conn=DBUtil.getConn();
            QueryRunner qr=new QueryRunner();
            return qr.query(conn,sql, new ScalarHandler<T>(), params);

        }catch(Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        }finally {
            close(conn);
        }
    }

    /**
     * 查询一列数据
     * @param <T>
     * @param sql
     * @param params
     * @return
     */
    public static <T> List<T>  getColumn(String sql, Object ... params ) {
        Connection conn=null;

        try {
            conn=DBUtil.getConn();
            QueryRunner qr=new QueryRunner();
            return qr.query(conn,sql, new ColumnListHandler<T>(), params);

        }catch(Exception ex) {
            ex.printStackTrace();
            throw new RuntimeException(ex);
        }finally {
            close(conn);
        }
    }

    public static Connection getConn() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }


    public static void close(ResultSet rs, Statement stm ,Connection conn) {
        if(rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(stm!=null) {
            try {
                stm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Connection conn) {
        if(conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


}
